﻿using System.Linq.Expressions;
using Klear.Library.Helpers;
using SQLite;
using Klear.Library.Models;

namespace Klear.Library.Services;
    
public class DatabaseService : IDatabaseService
{
    private SQLiteAsyncConnection _connection;
    private readonly string _databasePath;

    public event EventHandler<IncomeAndExpensesModel> ExpenseAdded;
    public event EventHandler<IncomeAndExpensesModel> ExpenseDeleted; 
    public bool IsInitialized { get; private set; } // 数据库连接是否初始化

    public DatabaseService()
    {
        // 设置数据库路径
        _databasePath= PathHelper.GetLocalFilePath("klear.sqlite3");
        
    }

    public async Task InitializeAsync()
    {
        if (IsInitialized)
        {
            return; 
        }

        _connection = new SQLiteAsyncConnection(_databasePath);

        // await _connection.CreateTableAsync<IncomeAndExpensesModel>();
        // await _connection.CreateTableAsync<BudgetModel>();
        // await _connection.CreateTableAsync<CategoryModel>();
        //await _connection.CreateTableAsync<DailyInfoModel>();
        IsInitialized = true;

    }
    
    //测试方法，不必理会
    public async Task<List<IncomeAndExpensesModel>> GetAllRecordsAsync()
    {
        _connection??= new SQLiteAsyncConnection(_databasePath);

        return await _connection.Table<IncomeAndExpensesModel>().ToListAsync();
    }
    
    // 获取预算设置
    public async Task<BudgetModel> GetBudgetSettingsAsync()
    {
        if (!IsInitialized)
        {
            await InitializeAsync();
        }
        // await _connection.CreateTableAsync<BudgetModel>();
        var query = await _connection.Table<BudgetModel>()
            .OrderByDescending(p => p.StartDate)
            .Take(1)
            .ToListAsync();

        return query.Count > 0 ? query[0] : null;
    }

    // 更新预算设置
    public async Task UpdateBudgetSettingsAsync(BudgetModel budgetModel)
    {
        if (!IsInitialized)
        {
            await InitializeAsync();
        }
        
        await _connection.InsertAsync(budgetModel);
    }

    // 获取指定时间段的实际支出总和
    public async Task<decimal> GetActualSpendingAsync(DateTime startDate, DateTime endDate)
    {
        if (!IsInitialized)
        {
            await InitializeAsync();
        }
        decimal totalSpending = 0;
        var expenses = await _connection.Table<IncomeAndExpensesModel>()
            .Where(e => e.CreateTime >= startDate &&
                        e.CreateTime <= endDate &&
                        e.Title == "支出")
            .ToListAsync();

        foreach (var expense in expenses)
        {
            totalSpending += expense.Amount;
        }

        return totalSpending;
    }
    // 指定日期和类型的收支记录
    public async Task<IList<IncomeAndExpensesModel>> GetIncomeAndExpensesAsync(DateTime startDate, DateTime endDate, string? title = null)
    {
        if (!IsInitialized)
        {
            await InitializeAsync();
        }

        var query = _connection.Table<IncomeAndExpensesModel>()
            .Where(e => e.CreateTime >= startDate && e.CreateTime <= endDate);

        if (!string.IsNullOrEmpty(title))
        {
            query = query.Where(e => e.Title == title);
        }

        return await query.ToListAsync();
    }
    
    //筛选五个属性的收支记录（全局搜索时使用）
    public async Task<IList<IncomeAndExpensesModel>> SearchIncomeAndExpensesAsync(string title, decimal amount, string category, string notes, DateTime createDate)
    {
        if (!IsInitialized)
        {
            await InitializeAsync();
        }

        // 初始查询
        var query = _connection.Table<IncomeAndExpensesModel>();

        // 如果 title 不为 null 或空，加入过滤条件
        if (!string.IsNullOrEmpty(title))
        {
            query = query.Where(e => e.Title.Contains(title));
        }

        // 如果 amount 不为默认值（即不是 0），加入过滤条件
        if (amount != 0)
        {
            query = query.Where(e => e.Amount == amount);
        }

        // 如果 category 不为 null 或空，加入过滤条件
        if (!string.IsNullOrEmpty(category))
        {
            query = query.Where(e => e.Category.Contains(category));
        }

        // 如果 notes 不为 null 或空，加入过滤条件
        if (!string.IsNullOrEmpty(notes))
        {
            query = query.Where(e => e.Notes.Contains(notes));
        }

        // 如果 createDate 不为默认值（即不是 DateTime.MinValue），加入过滤条件
        if (createDate != DateTime.MinValue)
        {
            query = query.Where(e => e.CreateTime.Date == createDate.Date);
        }

        // 返回查询结果
        return await query.ToListAsync();
    }

    
    // 数据加载
    public async Task<IList<IncomeAndExpensesModel>> LoadIncomeAndExpensesAsync(DateTime startDate, DateTime endDate)
    {
        return await GetIncomeAndExpensesAsync(startDate, endDate);
    }

    //新增收支
    public async Task AddRecordAsync(IncomeAndExpensesModel incomeAndExpenses)
    {
        if (!IsInitialized)
        {
            await InitializeAsync();
        }

        await _connection.InsertAsync(incomeAndExpenses);
        
        // 仅在添加支出记录时触发事件
        if (incomeAndExpenses.Title == "支出")
        {
            ExpenseAdded?.Invoke(this, incomeAndExpenses);
        }
    }
    
    //删除
    //收支删除
    public async Task Delete(IncomeAndExpensesModel data)
    {
        await _connection.DeleteAsync(data);
        
        // 仅在删除支出记录时触发事件
        if (data.Title == "支出")
        {
            ExpenseDeleted?.Invoke(this, data);
        }
    }
    
    // 判断数据库是否被初始化的函数
    public bool IsDatabaseInitialized() {
        Console.WriteLine(File.Exists(_databasePath));
        return File.Exists(_databasePath);
    }

    public async Task<IList<CategoryModel>> GetCategoriesAsync() {
        if (!IsInitialized) {
            await InitializeAsync();
        }
        
        var result = await _connection.Table<CategoryModel>().ToListAsync();
        Console.WriteLine("Got categories: " );
        foreach (var c in result) {
            Console.WriteLine(c.CategoryName);
        }
        return result;
    }

    public async Task<IList<CategoryModel>> GetExpenseCategoriesAsync() {
        if (!IsInitialized) {
            await InitializeAsync();
        }
        
        var result = await _connection.Table<CategoryModel>().
            Where(c => c.CategoryType==CategoryType.Expense).ToListAsync();
        Console.WriteLine("Got Expense Categories: ");
        foreach (var c in result) {
            Console.WriteLine(c.CategoryName);
        }
        return result;
    }
    

    public async Task<IList<CategoryModel>> GetIncomeCategoriesAsync() {
        if (!IsInitialized) {
            await InitializeAsync();
        }
        
        var result = await _connection.Table<CategoryModel>().
            Where(c => c.CategoryType==CategoryType.Income).ToListAsync();
        Console.WriteLine("Got Income Categories: ");
        foreach (var c in result) {
            Console.WriteLine(c.CategoryName);
        }
        return result;
    }

    public async Task AddCategoryAsync(CategoryModel categoryModel) {
        if (!IsInitialized) {
            await InitializeAsync();
        }    
        await _connection.InsertAsync(categoryModel);
    }

    public async Task UpdateCategoryAsync(CategoryModel categoryModel) {
        if (!IsInitialized) {
            await InitializeAsync();
        }
        await _connection.UpdateAsync(categoryModel);
    }
    
    public async Task DeleteCategoryAsync(CategoryModel categoryModel) {
        if (!IsInitialized) {
            await InitializeAsync();
        }

        await _connection.DeleteAsync(categoryModel);
    }

    // 获取预算设置
    public async Task<List<BudgetModel>> GetVisualizationBudgetSettingsAsync()
    {
        if (!IsInitialized)
        {
            await InitializeAsync();
        }
        
        var query = await _connection.Table<BudgetModel>()
            .OrderByDescending(p => p.StartDate)
            .Take(14)
            .ToListAsync();
    
        return query;
    }
    
    public async Task<bool> HasOverlappingBudget(DateTime startDate, DateTime endDate, int? excludeId = null)
    {
        if (!IsInitialized)
        {
            await InitializeAsync();
        }
    
        var query = _connection.Table<BudgetModel>()
            .Where(b => 
                // 检查是否存在重叠的时间段
                (startDate <= b.EndDate && endDate >= b.StartDate));
    
        // 如果提供了要排除的ID，则排除该预算记录
        if (excludeId.HasValue)
        {
            query = query.Where(b => b.ID != excludeId.Value);
        }
    
        var overlappingBudgets = await query.ToListAsync();
        return overlappingBudgets.Any();
    }
    
    public async Task<List<BudgetModel>> GetCurrentAndFutureBudgetsAsync()
    {
        if (!IsInitialized)
        {
            await InitializeAsync();
        }
    
        var currentDate = DateTime.Today;
        return await _connection.Table<BudgetModel>()
            .Where(b => b.EndDate >= currentDate)
            .OrderBy(b => b.StartDate)
            .ToListAsync();
    }

    public async Task DeleteBudgetAsync(BudgetModel budget)
    {
        if (!IsInitialized)
        {
            await InitializeAsync();
        }
    
        await _connection.DeleteAsync(budget);
    }
    
    //每日简讯
    public async Task AddDailyInfo(DailyInfoModel dailyInfoModel)
    {
        if (!IsInitialized)
        {
            await InitializeAsync();
        }

        await _connection.InsertAsync(dailyInfoModel);
    }
    public async Task<DailyInfoModel> FindTodayInfo(DateTime today)
    {
        if (!IsInitialized)
        {
            await InitializeAsync();
        }
        return await _connection.Table<DailyInfoModel>()
            .Where(e => e.Date == today)
            .FirstOrDefaultAsync();
    }
    
    public async Task<BudgetModel> GetCurrentMonthBudgetAsync()
    {
        if (!IsInitialized)
        {
            await InitializeAsync();
        }
    
        var today = DateTime.Today;
        var query = await _connection.Table<BudgetModel>()
            .Where(b => b.StartDate <= today && b.EndDate >= today)
            .ToListAsync();

        return query.FirstOrDefault();
    }
}
public static class DatabaseStorageConstant
{
    public const string VersionKey =
        nameof(DatabaseStorageConstant) + "." + nameof(Version);
    public const int Version = 1;
}

